package com.kevin.biz.base.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.kevin.utility.common.Page;


public class BaseJdbcDao {
	private static final Logger logger = Logger.getLogger(BaseJdbcDao.class);
	@Resource
	JdbcTemplate jdbcTemplate;
	
	public Page query(String sql, Object[] params, Page page){
		page.setTotalCount(getCount(sql, params));
		int start = (page.getPageNo()-1)*page.getPageSize();
    	page.setStartNo(start+1);
    	int endNo = start + page.getPageSize();
		page.setEndNo(endNo > page.getTotalCount().intValue() ? page.getTotalCount().intValue():endNo);
		StringBuilder sb = new StringBuilder();
		if(page.getPageSize() >0){
			sb.append(sql).append(" limit ").append(start).append(" , ").append(page.getPageSize()); 
		}
		page.setResult(this.query(sb.toString(),params));
		return page;
	}
	
	public Page query(String sql,  Page page){
		page.setTotalCount(getCount(sql));
		int start = (page.getPageNo()-1)*page.getPageSize();
    	page.setStartNo(start+1);
    	int endNo = start + page.getPageSize();
		page.setEndNo(endNo > page.getTotalCount().intValue() ? page.getTotalCount().intValue():endNo);
		StringBuilder sb = new StringBuilder();
		if(page.getPageSize() >0){
			sb.append(sql).append(" limit ").append(start).append(" , ").append(page.getPageSize()); 
		}
		page.setResult(this.query(sb.toString()));
		return page;
	}
	@SuppressWarnings("rawtypes")
	public List<LinkedHashMap> query(String sql,Object[] objects) {
		final List<LinkedHashMap> list = new ArrayList<LinkedHashMap>();
		jdbcTemplate.query(sql,objects, new RowCallbackHandler() {
			@SuppressWarnings("unchecked")
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				LinkedHashMap map = new LinkedHashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) 
					map.put(rsmd.getColumnLabel(i), rs.getString(rsmd.getColumnLabel(i)));
				list.add(map);
				map = null;
				rsmd = null;
			}
		});
		return list;
	}
	public List<LinkedHashMap<String, String>> query(String sql) {
		final List<LinkedHashMap<String, String>> list = new ArrayList<LinkedHashMap<String, String>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnLabel(i);
					try{
						String value = rs.getString(columnName);
						map.put(columnName, value);
					}catch(Exception e){
						logger.error(e.getMessage(), e);
						map.put(columnName, null);
					}
				}
				list.add(map);
				map = null;
				rsmd = null;
			}
		});
		return list;
	}
	public List<Map<String, Object>> queryListMap(String sql) {
		final List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnLabel(i);
					try{
						String value = rs.getString(columnName);
						map.put(columnName, value);
					}catch(Exception e){
						logger.error(e.getMessage(), e);
						map.put(columnName, null);
					}
				}
				list.add(map);
				map = null;
				rsmd = null;
			}
		});
		return list;
	}
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
    public String getCountSql(final String sql) {
        StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
        stringBuilder.append("select count(*) from (");
        stringBuilder.append(sql);
        stringBuilder.append(") tmp_count");
        return stringBuilder.toString();
    }
	
    public Integer getCount(final String sql){
    	return jdbcTemplate.queryForObject(getCountSql( sql), Integer.class);
    }
    public Integer getCount(final String sql, Object[] params){
    	return jdbcTemplate.queryForObject(getCountSql( sql),Integer.class, params);
    }
}
